package com.zz.comframe;

/**
 * 通用数据库操作
 * 包括查询、插入、删除等
 * 要求：目标表fieldename为“col0”（固定为id，使用uuid），“col1”，“col3”。。。
 * 可以安页查询，page输出视图，grid的columnmodle参数在gridheader表中
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import treemap.zz.json.JSONArray;
import treemap.zz.json.JSONObject;

import com.zz.comframe.properties.PropertiesPool;


public class CommonDataProcesser {
	private Connection connection = null;
	private PreparedStatement pStatement = null;
	private ResultSet resultSet = null;
	private static DBHelp db = new DBHelp();

	// public List<List<Map<String, Object>>> getObjectListAll(String sql,
	public JSONArray getObjectListAll(String sql, Object... obj) {
		int[] pagedata = { 0, 0, 0 };
		return getObjectListByNum(pagedata, sql, obj);

	}

	/**
	 * 
	 * @param pagedata
	 *            [0]分页开始,[1]每页数量,[2]总记录数（返回）,如果“[1]每页数量”为0，表示返回全部数量
	 * @param sql
	 * @param obj
	 * @return
	 * @throws
	 */
	public JSONArray getObjectListByNum(int[] pagedata, String sql, Object... obj) {

		// 单独用where子句处理条件
		//sql = obj!=null ? Constants.getSQLWhere(sql, "where 1=1", obj) : sql;	//存在问题，pagequery传来的where已含where
		if(obj!=null && obj.length>0)	System.out.println("obj参数非空，暂缺where处理");
		JSONArray jsonarr = new JSONArray();
		if (pagedata[0] <= 0 && pagedata[1] <= 0) {
			pagedata[0] = 0;
			pagedata[1] = 0;
		}
		pagedata[2] = 0;
		try {
			System.out.println("sql: " + sql);
			// System.out.println("par: "+obj!=null ?obj.toString():"null");
			connection = db.getConnection();
			pStatement = connection.prepareStatement(sql);
			// pStatement = formatSql(pStatement, obj);
			resultSet = pStatement.executeQuery();
			ResultSetMetaData rsmd = resultSet.getMetaData();
			String[] columnName = getColumnName(rsmd);
			int li = pagedata[1];
			int st = li == 0 ? 0 : pagedata[0]; // if limit is 0 then
			boolean all = li == 0 ? true : false; // return all recode
			while (resultSet.next()) {
				JSONObject json = new JSONObject();
				pagedata[2]++;
				if (st-- > 0)
					continue; // page begin ,>0 means not goto the begin line
				if (!all && li-- <= 0)
					continue; // page end,limit==0 is get all --???
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					Object o = resultSet.getObject(i + 1);
					if (o != null && !(o.toString().trim().equals(""))) {
						// java.sql.Date,java.lang.String,java.lang.Integer
						String t = o.getClass().getName().toLowerCase();
						String colname = columnName[i].toLowerCase().trim(); // field name to lowercase
						if (t.indexOf("string") >= 0) {
							if (o.toString().toLowerCase().trim().indexOf("true") >= 0)
								json.put(colname, true);
							else if (o.toString().toLowerCase().trim().indexOf("false") >= 0)
								json.put(colname, false);
							else
								json.put(colname, o);
						} else
							json.put(colname, o);
					}
				}
				jsonarr.put(jsonarr.length(),json);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeResource(connection, pStatement, resultSet);
		}
		return jsonarr;
	}

	/**
	 * 封装成页 int start, 分页起始 int limit, 分页长度 String sort, 排序关键字 String dir
	 * 升序或降序ASC(DESC) 因为数据库sql各异，所以在数据不多情况下不要sql查询分页， 而在本函数中封装分页
	 * 
	 * 2012-4-25加入默认排序，即“学号”排序任何时候都存在
	 */
	public Page PageQuery(String sql, int p, int limit, String sort, String dir) {
		String objs = null;
		int start = limit * (p - 1);

		// //select * from student where 1=1 order by ? ?;
		dir = dir!=null ? dir.trim() : "";
		if (sort != null && !sort.equals("")) {
			if(PropertiesPool.getCommonpro("DEFAULT_SORT_COLUMN_NAME")==null || sort.equalsIgnoreCase(PropertiesPool.getCommonpro("DEFAULT_SORT_COLUMN_NAME")))
				sort += "".equals(dir)?"":" "+dir;
			else
				sort +=  "".equals(dir)?"":" "+dir +"," + PropertiesPool.getCommonpro("DEFAULT_SORT_COLUMN_NAME") ;
		}else
			sort = PropertiesPool.getCommonpro("DEFAULT_SORT_COLUMN_NAME");
			sql = sql.toLowerCase().indexOf("order by") >= 0 ? sql.substring(0, sql.toLowerCase().indexOf("order by")) : sql;
			sql += " order by " + sort  + " ";


		int[] pagedata = { 0, 0, 0 }; // [0]分页开始,[1]每页数量,[2]总记录数（返回）
		if (start >= 0 && limit > 0) {
			pagedata[0] = start;
			pagedata[1] = limit;
		}
		JSONArray l = this.getObjectListByNum(pagedata, sql, objs != null ? objs.split(",") : null);
		Page page = new Page(pagedata[2], p, l);
		return page;
	}

	/**
	 * sql查询分页版本,不适用myeclipse derby，oracle或derby 10.x 封装成页 int start, 分页起始 int
	 * limit, 分页长度 String sort, 排序关键字 String dir 升序或降序ASC(DESC) 本函数在SQL中封装分页
	 */
	public Page PageQueryOnOracle(String sql, int p, int limit, String sort, String dir) {
		String objs = null;
		int totalcount = 0;
		int start = (limit * (p - 1)) + 1;
		;

		sql += " AND rownum > " + start + " AND rownum <= " + (start + limit) + " ";
		// sql.toLowerCase().replaceFirst("select","select limit "+start+" "+limit+" ");
		// SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*
		// FROM myLargeTable) AS tmp WHERE 1=1 /*AND rownum > 200000 AND rownum
		// <= 200005;*/
		if (sort != null && !sort.equals("") && dir != null && !dir.equals("")) {
			sql += " order by ? ? ";
			objs = sort.toString();
			objs += "," + dir;
		}

		// int[] pagedata = { 0, 0, 0 };
		// List<List<Map<String, Object>>> l = this.getObjectListAll(sql, objs
		// != null ? objs.split(",") : null);
		JSONArray l = this.getObjectListAll(sql, objs != null ? objs.split(",") : null);
		Page page = new Page(l.length(), p, l);
		return page;
	}

	private String[] getColumnName(ResultSetMetaData rsmd) {
		String[] columnName = null;
		try {
			columnName = new String[rsmd.getColumnCount()];
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				// columnName[i] = rsmd.getColumnTypeName(i + 1);
				columnName[i] = rsmd.getColumnName(i + 1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnName;
	}

	private PreparedStatement formatSql(PreparedStatement pStatement, Object... obj) {
		try {
			if (obj != null && obj.length != 0) {
				for (int i = 0; i < obj.length; i++) {
					pStatement.setObject(i + 1, obj[i]);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pStatement;
	}

	public boolean dosql(String sql, Object... obj) {
		boolean result = false;

		try {
			connection = db.getConnection();
			pStatement = connection.prepareStatement(sql);
			pStatement = formatSql(pStatement, obj);
			// resultSet = pStatement.executeQuery();
			pStatement.executeUpdate();
			db.closeResource(connection, pStatement);
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeResource(connection, pStatement, resultSet);
		}
		return result;
	}

	public void insert(String sql, String[] cols, Object[] obj) {// 加入UUid
		// sql source "INSERT INTO STUDENT (?) VALUES (?) "
		// "insert into student(uuid,code,name,sex,age,political,origin,professional) values(?,?,?,?,?,?,?)";
		if (cols.length != obj.length)
			return;

		String colstr = cols[0];
		String strpars = "??????????????????????????????????????????????????????????????????????????????????????????".substring(0, obj.length); // "?,?,?,?,?.."
		strpars = strpars.replaceAll("\\?\\?", "\\?,\\?");
		strpars = strpars.replaceAll("\\?\\?", "\\?,\\?");

		for (int i = 1; i < cols.length; i++) {
			colstr += "," + cols[i];
		}
		sql = sql.replaceFirst("\\(col\\)", "\\(" + colstr + "\\)");
		dosql(sql.replaceAll("\\?", strpars), obj);
	}

	public void update(String sql, Object... obj) {
		// String sql =
		// "update student set code=?,name=?,sex=?,age=?,political=?,origin=?,professional=? where id=?";
		dosql(sql, obj);
	}

	public void remove(String sql, Object... obj) {
		// String sql = "delete from student where id=?";

		dosql(sql, obj);
	}
}
